<?xml version="1.0" ?>

<xsl:stylesheet   xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
            xmlns:lzc="http://schemas.icitadel.com/Lazarus/Compiled.xsd"
                  version="1.0">

  <xsl:output method="text"/>

  <xsl:include href="../../common.xsl" />
  <xsl:include href="../../convert.xsl" />

  <xsl:template match="lzc:project">
    <xsl:apply-templates select="//lzc:method[ ( @compiling ) ]" />
  </xsl:template>

  <xsl:template match="lzc:method[ @compiling ]">/*
* Copyright 2012 Google Inc. All Rights Reserved.
*
* __author__ = joemu@google.com (Joe Allan Muharsky)
*
* <xsl:choose>
      <xsl:when test="@methodtype = 'Get'">
        <xsl:text>This procedure returns a single </xsl:text>
        <xsl:value-of select="ancestor::lzc:table/@friendly-name" />
        <xsl:text> from the database,
*   based on a specific primary key value.</xsl:text>
      </xsl:when>
      <xsl:when test="@methodtype = 'List'">
        <xsl:text>This procedure returns a list of all </xsl:text>
        <xsl:value-of select="ancestor::lzc:table/@friendly-name" />
        <xsl:text> from the database.</xsl:text>
      </xsl:when>
      <xsl:when test="@methodtype = 'Update'">
        <xsl:text>This procedure updates a single </xsl:text>
        <xsl:value-of select="ancestor::lzc:table/@friendly-name" />
        <xsl:text> in the database.</xsl:text>
      </xsl:when>
      <xsl:when test="@methodtype = 'Create'">
        <xsl:text>This procedure creates a new single </xsl:text>
        <xsl:value-of select="ancestor::lzc:table/@friendly-name" />
        <xsl:text> in the database.</xsl:text>
      </xsl:when>
      <xsl:when test="@methodtype = 'Custom'">
        <xsl:value-of select="lzc:description" />
      </xsl:when>
    </xsl:choose>
*
*/

DELIMITER //

DROP PROCEDURE IF EXISTS <xsl:value-of select="@db-name" />;//

CREATE PROCEDURE <xsl:value-of select="@db-name" />
  <xsl:choose>
    <xsl:when test="lzc:method-params/lzc:method-param[ @direction = 'in' ]">
    <xsl:text> (
  </xsl:text>
    <xsl:for-each select="lzc:method-params/lzc:method-param[ @direction = 'in' ]">
        <xsl:variable name="ParameterName">
          <xsl:value-of select="@dbname" />
        </xsl:variable>
        <xsl:if test="position() &gt; 1"><xsl:text>,
  </xsl:text>
        </xsl:if>
        <xsl:value-of select="$ParameterName" />
        <xsl:value-of select="$space" />
        <xsl:call-template name="getSqlParam">
          <xsl:with-param name="pFieldType" select="@type" />
        </xsl:call-template>
    </xsl:for-each>
)</xsl:when>
    <xsl:otherwise>()</xsl:otherwise>
  </xsl:choose>
    <xsl:text>
BEGIN

</xsl:text>
    <xsl:variable name="vObjectAlias">
      <xsl:value-of select="ancestor::lzc:table/@alias" />
    </xsl:variable>

    <xsl:choose>
      <xsl:when test="( @methodtype = $METHODTYPE_GET ) or ( @methodtype = $METHODTYPE_LIST )">
        <xsl:text>  SELECT
</xsl:text>
        <xsl:for-each select=".//lzc:method-result">
          <xsl:text>    </xsl:text>
          <xsl:value-of select="ancestor::lzc:object-ref/@alias" />
          <xsl:text>.</xsl:text>
          <xsl:value-of select="@dbname" />
          <xsl:variable name="RESULT_POSITION" select="position()" />
          <xsl:variable name="RESULT_COUNT" select="count( ancestor::lzc:method-results//lzc:method-result )" />
          <xsl:if test="$RESULT_POSITION &lt; $RESULT_COUNT">
            <xsl:text>,</xsl:text>
          </xsl:if>
          <xsl:text>
</xsl:text>
        </xsl:for-each>
        <xsl:text>  FROM</xsl:text>
        <xsl:variable name="TABLE_CONTEXT" select="lzc:method-results/lzc:object-ref[ position() = 1 ]" />
        <xsl:for-each select=".//lzc:object-ref">
        <xsl:text>
    </xsl:text>
          <xsl:if test="position() &gt; 1">
            <xsl:choose>
              <xsl:when test="@required = 'True'">
                <xsl:text>
  INNER JOIN </xsl:text>
              </xsl:when>
              <xsl:otherwise>
                <xsl:text>
  LEFT JOIN </xsl:text>
              </xsl:otherwise>
            </xsl:choose>
          </xsl:if>
          <xsl:value-of select="@ref" />
          <xsl:text> </xsl:text>
          <xsl:value-of select="@alias" />
          <xsl:if test="position() &gt; 1">
            <xsl:text> ON </xsl:text>
            <xsl:value-of select="$TABLE_CONTEXT/@alias" />
            <xsl:text>.</xsl:text>
            <xsl:value-of select="@fkfield" />
            <xsl:text> = </xsl:text>
            <xsl:value-of select="@alias" />
            <xsl:text>.</xsl:text>
            <xsl:value-of select="@pkfield" />
          </xsl:if>
        </xsl:for-each>
        <xsl:if test="count( lzc:method-filters/lzc:method-filter ) &gt; 0">
          <xsl:text>
  WHERE</xsl:text>
          <xsl:for-each select="lzc:method-filters/lzc:method-filter">
            <xsl:variable name="PARAM_NAME" select="@param" />
            <xsl:variable name="PARAM" select="ancestor::lzc:method/lzc:method-params/lzc:method-param[ @name = $PARAM_NAME ]" />
            <xsl:variable name="ParameterName">
              <xsl:value-of select="$PARAM/@dbname" />
            </xsl:variable>
            <xsl:text>
    </xsl:text>
            <xsl:value-of select="$vObjectAlias" />
            <xsl:text>.</xsl:text>
            <xsl:value-of select="@dbname" />
            <xsl:choose>
              <xsl:when test="@value">
                <xsl:variable name="VALUE_FIELD_NAME" select="@field" />
                <xsl:variable name="VALUE_FIELD" select="ancestor::lzc:table/lzc:fields/lzc:field[ @name = $VALUE_FIELD_NAME ]" />

                <xsl:choose>
                  <xsl:when test="@value = 'NULL'">
                    <xsl:text> IS NULL</xsl:text>
                  </xsl:when>
                  <xsl:otherwise>
                    <xsl:text> = </xsl:text>
                    <xsl:choose>
                      <xsl:when test="$VALUE_FIELD/@type = $DATATYPE_VARCHAR">
                        <xsl:text>'</xsl:text>
                        <xsl:value-of select="@value" />
                        <xsl:text>'</xsl:text>
                      </xsl:when>
                      <xsl:otherwise>
                        <xsl:value-of select="@value" />
                      </xsl:otherwise>
                    </xsl:choose>
                  </xsl:otherwise>
                </xsl:choose>
              </xsl:when>
              <xsl:otherwise>
                <xsl:text> = </xsl:text>
                <xsl:value-of select="$ParameterName" />
              </xsl:otherwise>
            </xsl:choose>
            <xsl:if test="position() &lt; count( ../lzc:method-filter )">
              <xsl:text> AND</xsl:text>
            </xsl:if>
          </xsl:for-each>
        </xsl:if>
        <xsl:text>;
</xsl:text>
      </xsl:when>

      <!-- Create and Update Method Implementation -->
      <xsl:when test="( @methodtype = $METHODTYPE_CREATE ) or ( @methodtype = $METHODTYPE_UPDATE )">

        <xsl:variable name="CURRENT_METHOD" select="." />
        <xsl:call-template name="CreateSQLStatement">
          <xsl:with-param name="pMethod" select="$CURRENT_METHOD" />
        </xsl:call-template>

      </xsl:when>

    </xsl:choose>
    <xsl:text>
END//
</xsl:text>

</xsl:template>

  <xsl:template name="CreateSQLStatement">
    <xsl:param name="pMethod" />

    <xsl:for-each select="$pMethod/lzc:method-effects/lzc:object-ref">
      <xsl:choose>
        <xsl:when test="@effecttype = $METHODTYPE_CREATE">
          <!-- Assign non-Identity Autogen values first -->
          <xsl:for-each select="lzc:fields/lzc:method-effect[ @autogenerate = 'True' and not( @type = $DATATYPE_IDENTITY ) ]">

            <xsl:variable name="ParameterName">
              <xsl:if test="ancestor::lzc:project/use-parameter-prefix">p</xsl:if>
              <xsl:value-of select="@param" />
            </xsl:variable>
            <xsl:text>  SET </xsl:text>
            <xsl:value-of select="$ParameterName" />
            <xsl:text> = </xsl:text>
            <xsl:choose>
              <xsl:when test="@type = $DATATYPE_GUID">
                <xsl:text>NEWID()</xsl:text>
              </xsl:when>
              <xsl:when test="@type = $DATATYPE_DATETIME">
                <xsl:text>CURRENT_TIMESTAMP</xsl:text>
              </xsl:when>
            </xsl:choose>
            <xsl:text>;
</xsl:text>

          </xsl:for-each> INSERT <xsl:value-of select="@ref" />
  (<xsl:for-each select="lzc:fields/lzc:method-effect">
  <xsl:if test="position() &gt; 1">,</xsl:if>
  <xsl:text>
    </xsl:text>
  <xsl:value-of select="@dbname" />
</xsl:for-each>
  )
  VALUES
  (<xsl:for-each select="lzc:fields/lzc:method-effect">
  <xsl:if test="position() &gt; 1">,</xsl:if>
  <xsl:text>
    </xsl:text>
  <xsl:value-of select="@dbname" />
</xsl:for-each>
  );
</xsl:when> <!-- End CREATE method -->

<xsl:when test="@effecttype = $METHODTYPE_UPDATE">  UPDATE <xsl:value-of select="@ref" /><xsl:text> SET</xsl:text>
          <xsl:for-each select="lzc:fields/lzc:method-effect">
            <xsl:if test="position() &gt; 1">,</xsl:if>
            <xsl:variable name="PARAM_NAME" select="@param" />
            <xsl:variable name="PARAM" select="ancestor::lzc:method/lzc:method-params/lzc:method-param[ @name = $PARAM_NAME ]" />
            <xsl:text>
    </xsl:text>
            <xsl:value-of select="@dbname" /> = <xsl:value-of select="@dbname" />
          </xsl:for-each>
          <xsl:if test="count( lzc:method-filters/lzc:method-filter ) &gt; 0">
            <xsl:text>
  WHERE</xsl:text>
            <xsl:for-each select="lzc:method-filters/lzc:method-filter">
              <xsl:if test="position() &gt; 1"> AND</xsl:if>
              <xsl:variable name="PARAM_NAME" select="@param" />
              <xsl:variable name="PARAM" select="ancestor::lzc:method/lzc:method-params/lzc:method-param[ @name = $PARAM_NAME ]" />
              <xsl:text>
    </xsl:text>
              <xsl:value-of select="@dbname" />
              <xsl:text></xsl:text>
              <xsl:text> = </xsl:text>
              <xsl:value-of select="@dbname" />
            </xsl:for-each>;
</xsl:if>
        </xsl:when> <!--  End UPDATE method -->
      </xsl:choose>
    </xsl:for-each>

<xsl:variable name="OUTPUT_PARAMS" select="lzc:method-params/lzc:method-param[ @direction = 'out' ]" />
<xsl:if test="count($OUTPUT_PARAMS) &gt; 0">
  # Return output parameters as a result set.
  SELECT<xsl:for-each select="$OUTPUT_PARAMS">
  <xsl:if test="position() &gt; 1">,</xsl:if>
  <xsl:text>
    </xsl:text>
  <xsl:choose>
    <xsl:when test="@autogenerate = 'True' and @type = 'Int'">LAST_INSERT_ID()</xsl:when>
    <xsl:otherwise>UNSUPPORTED_PARAMETER</xsl:otherwise>
  </xsl:choose>
  <xsl:text> AS </xsl:text>
  <xsl:value-of select="@name" />
  </xsl:for-each>;
</xsl:if>
</xsl:template>

</xsl:stylesheet>